package com.zyh.day04.sql

import org.apache.spark.sql.{DataFrame, SparkSession}

object WhereTest {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder()
      .appName("where")
      .master("local[*]")
      .getOrCreate()
    //RDD[case class] --> DataFrame
    //Scala集合[case class] --> DataFrame
    val list = List((1, "xiaohei", 18, "1"), (2, "xiaobai", 20, "2"))

    import spark.implicits._
    val df: DataFrame = list.toDF("id", "name", "age", "sex")
    df.createOrReplaceTempView("t_user")

//    spark.sql(
//      """
//        |select distinct sex
//        |from t_user
//        |where name like '%xiao%'
//        |order by age desc
//        |limit 1
//        |""".stripMargin).show()
    val sql =
      """
        |select id,name,age,sex,
        |case sex
        |when 1 then '男'
        |when 2 then '女'
        |else '其他'
        |end as sex2
        |from t_user
        |""".stripMargin
    spark.sql(sql).show()
    spark.close()
  }
}
